IF OBJECT_ID('dbo.csp_rptMetric_SummaryDifference') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.csp_rptMetric_SummaryDifference
END
GO

CREATE PROCEDURE dbo.csp_rptMetric_SummaryDifference
(
	@PeriodType varchar(50)
,	@PeriodStartDate smalldatetime
,	@ComparisonPeriodStartDate smalldatetime
)
AS

SELECT
	Institution = COALESCE(x1.Institution, x2.Institution)
,	PendingSubmissions = ISNULL(x1.PendingSubmissions,0) - ISNULL(x2.PendingSubmissions,0)
,	AwardsActivated = ISNULL(x1.AwardsActivated,0) - ISNULL(x2.AwardsActivated,0)
,	AwardsToBeActivated = ISNULL(x1.AwardsToBeActivated,0) - ISNULL(x2.AwardsToBeActivated,0)
,	PCROAwardsToBeActivated = ISNULL(x1.PCROAwardsToBeActivated,0) - ISNULL(x2.PCROAwardsToBeActivated,0)
,	PreAwardJIT = ISNULL(x1.PreAwardJIT,0) - ISNULL(x2.PreAwardJIT,0)
,	PreAwardJITWeek = ISNULL(x1.PreAwardJITWeek,0) - ISNULL(x2.PreAwardJITWeek,0)
,	AdvanceAccount = ISNULL(x1.AdvanceAccount,0) - ISNULL(x2.AdvanceAccount,0)
,	AdvanceAccountWeek = ISNULL(x1.AdvanceAccountWeek,0) - ISNULL(x2.AdvanceAccountWeek,0)
,	AwardedButNoAttachment = ISNULL(x1.AwardedButNoAttachment,0) - ISNULL(x2.AwardedButNoAttachment,0)
,	PCROAwardedButNoAttachment = ISNULL(x1.PCROAwardedButNoAttachment,0) - ISNULL(x2.PCROAwardedButNoAttachment,0)
,	AwardedButNotFunded = ISNULL(x1.AwardedButNotFunded,0) - ISNULL(x2.AwardedButNotFunded,0)
,	RenewalsProcessed = ISNULL(x1.RenewalsProcessed,0) - ISNULL(x2.RenewalsProcessed,0)
,	RenewalsToBeProcessed = ISNULL(x1.RenewalsToBeProcessed,0) - ISNULL(x2.RenewalsToBeProcessed,0)
,	AccountInactivated = ISNULL(x1.AccountInactivated,0) - ISNULL(x2.AccountInactivated,0)
,	ProjectsEnding30 = ISNULL(x1.ProjectsEnding30,0) - ISNULL(x2.ProjectsEnding30,0)
,	ProjectsEnding60 = ISNULL(x1.ProjectsEnding60,0) - ISNULL(x2.ProjectsEnding60,0)
,	ProjectsEnding90 = ISNULL(x1.ProjectsEnding90,0) - ISNULL(x2.ProjectsEnding90,0)
FROM
(
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	PendingSubmissions = SUM(CASE WHEN Subject='Pending Submissions' THEN 1 ELSE 0 END)
	,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
	,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PreAwardJIT = SUM(CASE WHEN Subject='Pre-Award JIT' THEN 1 ELSE 0 END)
	,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
	,	AdvanceAccount = SUM(CASE WHEN Subject='Advance Account' THEN 1 ELSE 0 END)
	,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
	,	AwardedButNoAttachment = SUM(CASE WHEN Subject='Awarded But No Attachment' THEN 1 ELSE 0 END)
	,	PCROAwardedButNoAttachment = SUM(CASE WHEN Subject='PCRO Awarded But No Attachment' THEN 1 ELSE 0 END)
	,	AwardedButNotFunded = SUM(CASE WHEN Subject='Awarded But Not Funded' THEN 1 ELSE 0 END)
	,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
	,	RenewalsToBeProcessed = SUM(CASE WHEN Subject='Renewals To Be Processed' THEN 1 ELSE 0 END)
	,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
	,	ProjectsEnding30 = SUM(CASE WHEN Subject='Projects Ending 30' THEN 1 ELSE 0 END)
	,	ProjectsEnding60 = SUM(CASE WHEN Subject='Projects Ending 60' THEN 1 ELSE 0 END)
	,	ProjectsEnding90 = SUM(CASE WHEN Subject='Projects Ending 90' THEN 1 ELSE 0 END)
	FROM
	(
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Accounts Inactivated'
		FROM
			[dbo].[rptMetric_AccountsInactivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account'
		FROM
			[dbo].[rptMetric_AdvanceAccount]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account Week'
		FROM
			[dbo].[rptMetric_AdvanceAccountWeek]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards Activated'
		FROM
			[dbo].[rptMetric_AwardActivations]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awarded But No Attachment'
		FROM
			[dbo].[rptMetric_AwardedButNoAttachment]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awarded But Not Funded'
		FROM
			[dbo].[rptMetric_AwardedButNotFunded]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards To Be Activated'
		FROM
			[dbo].[rptMetric_AwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awarded But No Attachment'
		FROM
			[dbo].[rptMetric_PCROAwardedButNoAttachment]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awards To Be Activated'
		FROM
			[dbo].[rptMetric_PCROAwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pending Submissions'
		FROM
			[dbo].[rptMetric_PendingSubmissions]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT'
		FROM
			[dbo].[rptMetric_PreAwardJIT]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT Week'
		FROM
			[dbo].[rptMetric_PreAwardJITWeek]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 30'
		FROM
			[dbo].[rptMetric_ProjectsEnding30]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 60'
		FROM
			[dbo].[rptMetric_ProjectsEnding60]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 90'
		FROM
			[dbo].[rptMetric_ProjectsEnding90]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals Processed'
		FROM
			[dbo].[rptMetric_RenewalsProcessed]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals To Be Processed'
		FROM
			[dbo].[rptMetric_RenewalsToBeProcessed]
	) AS x
	WHERE
		1=1
	AND	PeriodType = @PeriodType
	AND	PeriodStartDate = @PeriodStartDate
	GROUP BY
		Institution
	,	PeriodType
	,	PeriodStartDate
) AS x1
FULL OUTER JOIN
(
	SELECT
		Institution
	,	PeriodType
	,	PeriodStartDate
	,	PendingSubmissions = SUM(CASE WHEN Subject='Pending Submissions' THEN 1 ELSE 0 END)
	,	AwardsActivated = SUM(CASE WHEN Subject='Awards Activated' THEN 1 ELSE 0 END)
	,	AwardsToBeActivated = SUM(CASE WHEN Subject='Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PCROAwardsToBeActivated = SUM(CASE WHEN Subject='PCRO Awards To Be Activated' THEN 1 ELSE 0 END)
	,	PreAwardJIT = SUM(CASE WHEN Subject='Pre-Award JIT' THEN 1 ELSE 0 END)
	,	PreAwardJITWeek = SUM(CASE WHEN Subject='Pre-Award JIT Week' THEN 1 ELSE 0 END)
	,	AdvanceAccount = SUM(CASE WHEN Subject='Advance Account' THEN 1 ELSE 0 END)
	,	AdvanceAccountWeek = SUM(CASE WHEN Subject='Advance Account Week' THEN 1 ELSE 0 END)
	,	AwardedButNoAttachment = SUM(CASE WHEN Subject='Awarded But No Attachment' THEN 1 ELSE 0 END)
	,	PCROAwardedButNoAttachment = SUM(CASE WHEN Subject='PCRO Awarded But No Attachment' THEN 1 ELSE 0 END)
	,	AwardedButNotFunded = SUM(CASE WHEN Subject='Awarded But Not Funded' THEN 1 ELSE 0 END)
	,	RenewalsProcessed = SUM(CASE WHEN Subject='Renewals Processed' THEN 1 ELSE 0 END)
	,	RenewalsToBeProcessed = SUM(CASE WHEN Subject='Renewals To Be Processed' THEN 1 ELSE 0 END)
	,	AccountInactivated = SUM(CASE WHEN Subject='Accounts Inactivated' THEN 1 ELSE 0 END)
	,	ProjectsEnding30 = SUM(CASE WHEN Subject='Projects Ending 30' THEN 1 ELSE 0 END)
	,	ProjectsEnding60 = SUM(CASE WHEN Subject='Projects Ending 60' THEN 1 ELSE 0 END)
	,	ProjectsEnding90 = SUM(CASE WHEN Subject='Projects Ending 90' THEN 1 ELSE 0 END)
	FROM
	(
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Accounts Inactivated'
		FROM
			[dbo].[rptMetric_AccountsInactivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account'
		FROM
			[dbo].[rptMetric_AdvanceAccount]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Advance Account Week'
		FROM
			[dbo].[rptMetric_AdvanceAccountWeek]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards Activated'
		FROM
			[dbo].[rptMetric_AwardActivations]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awarded But No Attachment'
		FROM
			[dbo].[rptMetric_AwardedButNoAttachment]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awarded But Not Funded'
		FROM
			[dbo].[rptMetric_AwardedButNotFunded]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Awards To Be Activated'
		FROM
			[dbo].[rptMetric_AwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awarded But No Attachment'
		FROM
			[dbo].[rptMetric_PCROAwardedButNoAttachment]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'PCRO Awards To Be Activated'
		FROM
			[dbo].[rptMetric_PCROAwardsToBeActivated]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pending Submissions'
		FROM
			[dbo].[rptMetric_PendingSubmissions]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT'
		FROM
			[dbo].[rptMetric_PreAwardJIT]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Pre-Award JIT Week'
		FROM
			[dbo].[rptMetric_PreAwardJITWeek]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 30'
		FROM
			[dbo].[rptMetric_ProjectsEnding30]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 60'
		FROM
			[dbo].[rptMetric_ProjectsEnding60]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Projects Ending 90'
		FROM
			[dbo].[rptMetric_ProjectsEnding90]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals Processed'
		FROM
			[dbo].[rptMetric_RenewalsProcessed]
		UNION ALL
		SELECT
			Institution
		,	PeriodType
		,	PeriodStartDate
		,	Subject = 'Renewals To Be Processed'
		FROM
			[dbo].[rptMetric_RenewalsToBeProcessed]
	) AS x
	WHERE
		1=1
	AND	PeriodType = @PeriodType
	AND	PeriodStartDate = @ComparisonPeriodStartDate
	GROUP BY
		Institution
	,	PeriodType
	,	PeriodStartDate
) AS x2
	ON x1.Institution = x2.Institution

GO
